conn &usuario_sinawin./&senha_sinawin@&conexao

prompt "Conectado Sinawin"


grant SELECT, REFERENCES on TFWDIREITO to &empresa
/
grant SELECT, REFERENCES on TFWDIR_MOD_SIST to &empresa
/
grant SELECT, REFERENCES on TFWMENSAGEM to &empresa
/
grant SELECT, REFERENCES on TFWMENU to &empresa
/
grant SELECT, REFERENCES on TFWMODULOSINST to &empresa
/
grant SELECT, REFERENCES on TFWSPOL_ACES to &empresa
/
grant SELECT, REFERENCES on TFWTIPODIREITO to &empresa
/


conn &empresa./&senha_empresa@&conexao

prompt "Conectado Empresa"

  CREATE TABLE &empresa..TFWEPERM_POL_ACES 
   (	COD_PERM VARCHAR2(50) NOT NULL ENABLE, 
	COD_POL_ACES NUMBER NOT NULL ENABLE, 
	IND_PERM_EDIC_DADO NUMBER(1,0) NOT NULL ENABLE, 
	IND_VISI_DADO NUMBER(1,0) NOT NULL ENABLE, 
	 CONSTRAINT XPKTFWEPERM_POL_ACES PRIMARY KEY (COD_PERM, COD_POL_ACES)
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 311296 NEXT 311296 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE &tablespace_indice  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 311296 NEXT 311296 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE &tablespace_dados 
 /
  CREATE GLOBAL TEMPORARY TABLE &empresa..TMPFWFUNCOES 
   (	CD_FUNCAO CHAR(6)
   ) ON COMMIT PRESERVE ROWS 
 /
  CREATE GLOBAL TEMPORARY TABLE &empresa..TMPFWMENU 
   (	CD_MENU NUMBER NOT NULL ENABLE
   ) ON COMMIT PRESERVE ROWS 
 /
  CREATE GLOBAL TEMPORARY TABLE &empresa..TMPFWUSUGRPPER 
   (	CD_USUARIO NUMBER(6,0), 
	CD_GRUPO NUMBER(3,0), 
	ID_PERMISSAO NUMBER(3,0), 
	CD_SISTEMA VARCHAR2(4)
   ) ON COMMIT PRESERVE ROWS 
 /
CREATE OR REPLACE SYNONYM &empresa..TFWDIREITO FOR &usuario_sinawin..TFWDIREITO
/
CREATE OR REPLACE SYNONYM &empresa..TFWDIR_MOD_SIST FOR &usuario_sinawin..TFWDIR_MOD_SIST
/
CREATE OR REPLACE SYNONYM &empresa..TFWMENSAGEM FOR &usuario_sinawin..TFWMENSAGEM
/
CREATE OR REPLACE SYNONYM &empresa..TFWMENU FOR &usuario_sinawin..TFWMENU
/
CREATE OR REPLACE SYNONYM &empresa..TFWMODULOSINST FOR &usuario_sinawin..TFWMODULOSINST
/
CREATE OR REPLACE SYNONYM &empresa..TFWSPOL_ACES FOR &usuario_sinawin..TFWSPOL_ACES
/
CREATE OR REPLACE SYNONYM &empresa..TFWTIPODIREITO FOR &usuario_sinawin..TFWTIPODIREITO
/
  CREATE OR REPLACE PACKAGE &empresa..FUNC_FW AS
  type cursortype IS ref CURSOR;
  type rfuncao IS record(cd_funcao CHAR(6));
  type tfuncao IS TABLE OF rfuncao INDEX BY binary_integer;
  FUNCTION fnfw_retornaarvorefuncao(funcao IN CHAR,   sistema IN VARCHAR2) RETURN tfuncao;
END;
/
 
  CREATE OR REPLACE PACKAGE BODY &empresa..FUNC_FW IS FUNCTION fnfw_retornaarvorefuncao(funcao IN CHAR,   sistema IN VARCHAR2) RETURN tfuncao IS funsup CHAR(6);
  tf tfuncao;
  tf_ret tfuncao;
  rf rfuncao;
  v_index NUMBER(5);
  BEGIN
    begin
         select distinct cd_funsup
         into   funsup
         from   tgefuncao
         where  cd_funcao = funcao
         and    cd_sistema = sistema;
      exception
         when no_data_found then
            funsup := null;
      end;

    IF(funsup IS NOT NULL) THEN
      tf_ret := fnfw_retornaarvorefuncao(funsup,   sistema);
      FOR i IN tf_ret.FIRST .. tf_ret.LAST
      LOOP
        tf(i) := tf_ret(i);
      END LOOP;
    END IF;

    IF(tf.LAST IS NOT NULL) THEN
      v_index := tf.LAST + 1;
    ELSE
      v_index := 0;
    END IF;

    rf.cd_funcao := funcao;
    tf(v_index) := rf;
    RETURN tf;
  END fnfw_retornaarvorefuncao;
END;
/ 
  CREATE OR REPLACE PACKAGE &empresa..PROC_FW AS
  type cursortype IS ref CURSOR;
  PROCEDURE spfw_retornaroles(p_cod_sistema IN VARCHAR2,   p_cod_usuario IN NUMBER,   p_cursor OUT proc_fw.cursortype);
  PROCEDURE spfw_retornamodulos(p_cod_usuario IN NUMBER,   p_cursor OUT proc_fw.cursortype);
  PROCEDURE spfw_retornamenu(p_cod_sistema IN VARCHAR2,   p_cod_usuario IN NUMBER,   p_cursor OUT proc_fw.cursortype);
END;
/ 
  CREATE OR REPLACE PACKAGE BODY &empresa..PROC_FW IS PROCEDURE spfw_retornamenu(p_cod_sistema IN VARCHAR2,   p_cod_usuario IN NUMBER,   p_cursor OUT proc_fw.cursortype) IS
  rf func_fw.rfuncao;
  tf func_fw.tfuncao;
  tf_aux func_fw.tfuncao;
  v_index NUMBER(5);
  CURSOR p2_cursor IS
  SELECT DISTINCT fun.cd_funcao
  FROM tgefuncao fun LEFT JOIN tcasisusufun usufun ON fun.cd_funcao = usufun.cd_funcao
  INNER JOIN tmpfwusugrpper usugruper ON usugruper.cd_usuario = usufun.cd_usuario
   AND UPPER(usugruper.cd_sistema) = UPPER(usufun.cd_sistema)
  UNION
  SELECT DISTINCT fun.cd_funcao
  FROM tgefuncao fun LEFT JOIN tcasisgrpfun grufun ON fun.cd_funcao = grufun.cd_funcao
  INNER JOIN tmpfwusugrpper usugruper ON usugruper.cd_grupo = grufun.cd_grupo
   AND UPPER(usugruper.cd_sistema) = UPPER(grufun.cd_sistema)
  UNION
  SELECT DISTINCT fun.cd_funcao
  FROM tgefuncao fun LEFT JOIN tcaperfun perfun ON fun.cd_funcao = perfun.cd_funcao
  INNER JOIN tmpfwusugrpper usugruper ON usugruper.id_permissao = perfun.id_permissao
   AND UPPER(usugruper.cd_sistema) = UPPER(perfun.cd_sistema);
  BEGIN

    DELETE FROM tmpfwusugrpper;

    DELETE FROM tmpfwfuncoes;

    DELETE FROM tmpfwmenu;
    INSERT
    INTO tmpfwusugrpper
    SELECT NULL,
      usugru.cd_grupo,
      grpper.id_permissao,
      UPPER(sisgrp.cd_sistema)
    FROM tcausugrp usugru
    INNER JOIN tcasisgrp sisgrp ON usugru.cd_grupo = sisgrp.cd_grupo
     AND sisgrp.in_bloqueio = 'N' LEFT JOIN tcasisgrpper grpper ON grpper.cd_grupo = usugru.cd_grupo
     AND UPPER(sisgrp.cd_sistema) = UPPER(grpper.cd_sistema)
    WHERE usugru.cd_usuario = p_cod_usuario
     AND UPPER(sisgrp.cd_sistema) = UPPER(p_cod_sistema)
    UNION
    SELECT DISTINCT usu.cd_usuario,
      NULL,
      usuper.id_permissao,
      UPPER(sisusu.cd_sistema)
    FROM tgeusuario usu
    INNER JOIN tcasisusu sisusu ON usu.cd_usuario = sisusu.cd_usuario
     AND sisusu.in_bloqueio = 'N'
     AND UPPER(sisusu.cd_sistema) = UPPER(p_cod_sistema) LEFT JOIN tcasisusuper usuper ON usuper.cd_usuario = usu.cd_usuario
     AND UPPER(usuper.cd_sistema) = UPPER(p_cod_sistema)
    WHERE usu.cd_usuario = p_cod_usuario
     AND UPPER(sisusu.cd_sistema) = UPPER(p_cod_sistema);
    v_index := 0;
    FOR c1 IN p2_cursor
    LOOP
      tf_aux := func_fw.fnfw_retornaarvorefuncao(c1.cd_funcao,   UPPER(p_cod_sistema));
      FOR i IN tf_aux.FIRST .. tf_aux.LAST
      LOOP
        INSERT
        INTO tmpfwfuncoes
        VALUES(tf_aux(i).cd_funcao);
      END LOOP;
    END LOOP;

    INSERT
    INTO tmpfwmenu(cd_menu)
    SELECT DISTINCT menu.cd_menu
    FROM tfwmenu menu
    INNER JOIN tmpfwfuncoes tmpfuncoes ON(tmpfuncoes.cd_funcao = menu.cd_funcao)
    WHERE UPPER(menu.cd_sistema) = UPPER(p_cod_sistema)
    ORDER BY menu.cd_menu;

    OPEN p_cursor FOR
    SELECT DISTINCT tmp_menu.cd_menu,
      menu.cd_menusup,
      menu.ds_menu,
      menu.ds_assembly,
      ds_classe,
      menu.in_bloqueio,
      menu.cd_funcao
    FROM tmpfwmenu tmp_menu
    INNER JOIN tfwmenu menu ON(menu.cd_menu = tmp_menu.cd_menu)
    WHERE menu.in_bloqueio = 'N'
    ORDER BY tmp_menu.cd_menu;
  END spfw_retornamenu;
  PROCEDURE spfw_retornaroles(p_cod_sistema IN VARCHAR2,   p_cod_usuario IN NUMBER,   p_cursor OUT proc_fw.cursortype) IS
  rf func_fw.rfuncao;
  tf func_fw.tfuncao;
  tf_aux func_fw.tfuncao;
  v_index NUMBER(5);
  CURSOR p2_cursor IS
  SELECT DISTINCT fun.cd_funcao
  FROM tgefuncao fun LEFT JOIN tcasisusufun usufun ON fun.cd_funcao = usufun.cd_funcao
  INNER JOIN tmpfwusugrpper usugruper ON usugruper.cd_usuario = usufun.cd_usuario
   AND usugruper.cd_sistema = usufun.cd_sistema
  UNION
  SELECT DISTINCT fun.cd_funcao
  FROM tgefuncao fun LEFT JOIN tcasisgrpfun grufun ON fun.cd_funcao = grufun.cd_funcao
  INNER JOIN tmpfwusugrpper usugruper ON usugruper.cd_grupo = grufun.cd_grupo
   AND usugruper.cd_sistema = grufun.cd_sistema
  UNION
  SELECT DISTINCT fun.cd_funcao
  FROM tgefuncao fun LEFT JOIN tcaperfun perfun ON fun.cd_funcao = perfun.cd_funcao
  INNER JOIN tmpfwusugrpper usugruper ON usugruper.id_permissao = perfun.id_permissao
   AND usugruper.cd_sistema = perfun.cd_sistema;
  BEGIN

    DELETE FROM tmpfwusugrpper;

    DELETE FROM tmpfwfuncoes;
    INSERT
    INTO tmpfwusugrpper
    SELECT NULL,
      usugru.cd_grupo,
      grpper.id_permissao,
      sisgrp.cd_sistema
    FROM tcausugrp usugru
    INNER JOIN tcasisgrp sisgrp ON usugru.cd_grupo = sisgrp.cd_grupo
     AND sisgrp.in_bloqueio = 'N' LEFT JOIN tcasisgrpper grpper ON grpper.cd_grupo = usugru.cd_grupo
     AND sisgrp.cd_sistema = grpper.cd_sistema
    WHERE usugru.cd_usuario = p_cod_usuario
     AND sisgrp.cd_sistema = p_cod_sistema
    UNION
    SELECT DISTINCT usu.cd_usuario,
      NULL,
      usuper.id_permissao,
      sisusu.cd_sistema
    FROM tgeusuario usu
    INNER JOIN tcasisusu sisusu ON usu.cd_usuario = sisusu.cd_usuario
     AND sisusu.in_bloqueio = 'N' LEFT JOIN tcasisusuper usuper ON usuper.cd_usuario = usu.cd_usuario
     AND sisusu.cd_sistema = usuper.cd_sistema
    WHERE usu.cd_usuario = p_cod_usuario
     AND sisusu.cd_sistema = p_cod_sistema;
    v_index := 0;
    FOR c1 IN p2_cursor
    LOOP
      tf_aux := func_fw.fnfw_retornaarvorefuncao(c1.cd_funcao,   p_cod_sistema);
      FOR i IN tf_aux.FIRST .. tf_aux.LAST
      LOOP
        INSERT
        INTO tmpfwfuncoes
        VALUES(tf_aux(i).cd_funcao);
      END LOOP;
    END LOOP;

    OPEN p_cursor FOR
    SELECT DISTINCT cd_funcao
    FROM tmpfwfuncoes;
  END spfw_retornaroles;
  PROCEDURE spfw_retornamodulos(p_cod_usuario IN NUMBER,   p_cursor OUT proc_fw.cursortype) IS
  BEGIN

    OPEN p_cursor FOR
    SELECT DISTINCT sis.cd_sistema,
      sis.ds_sistema
    FROM tgesist sis
    INNER JOIN tgesinst inst ON inst.cd_sistema = sis.cd_sistema
    INNER JOIN tcasisusu sisusu ON sisusu.cd_sistema = sis.cd_sistema
    INNER JOIN tfwmodulosinst mdinst ON mdinst.cd_sistema = sis.cd_sistema
    WHERE sisusu.in_bloqueio = 'N'
     AND sis.in_bloqueio = 'N'
     AND sisusu.cd_usuario = p_cod_usuario
    UNION
    SELECT DISTINCT sis.cd_sistema,
      sis.ds_sistema
    FROM tgesist sis
    INNER JOIN tgesinst inst ON inst.cd_sistema = sis.cd_sistema
    INNER JOIN tcasisgrp sisgrp ON sisgrp.cd_sistema = sis.cd_sistema
    INNER JOIN tcausugrp usugrp ON usugrp.cd_grupo = sisgrp.cd_grupo
    WHERE sisgrp.in_bloqueio = 'N'
     AND sis.in_bloqueio = 'N'
     AND usugrp.cd_usuario = p_cod_usuario;
  END spfw_retornamodulos;
END;
/